想必大家都知道Google Form用來做系統報名是非常方便的工具,但...
如果報名人數有限制的話,Google Form並沒有一套檢查機制可以執行
但我們知道Google Form的資料會儲存在Google試算表內,所以可以通過Google試算表及內建的Google Script,
來顯示Google表單的某下拉選單 [如日期或是梯次] 來判定Google Form報名人數是否已滿
而且我們有時候希望報名的人能夠提前預約活動
本範例就在展示如何預約未來3-10天的Google報名...
步驟一
先建立一個Google 報名表單,需含有一個參加日期的下拉選單
步驟二
建立此Google Form的Google Sheet
步驟三
建立第二個Sheet活動梯次表,並建立如下欄位:
活動梯次/日期二/年度/月份/日期一/星期/額滿否/剩餘數量/容納數量/報名數量
並從選單->格式->數值->改成純文字
步驟四
建立活動梯次表Sheet內的資料與欄位相關語法
-1. 先填入[年度]、[月份]、[日期二]、[星期]的資料 ,這裡範例是先填寫2020/1 ~ 3 月
-2. [日期一]第一列的欄位公式 = C2&D2&E2,其餘欄位公式直接拖拉後依序產生
-3. [活動梯次]第一列公式 =C2&"/"&D2&"/"&E2&"("&F2&")" 其餘欄位公式直接拖拉後依序產生
-4. [容納數量]我們先填每梯次都是15人
-5. [報名數量]第一列公式 = sumifs('表單回應 1'!D:D,'表單回應 1'!B:B,A2)
這裡稍微要說明一下,我要加總的是表單回應1的D欄,也就是[報名人數]這一欄,但是必須符合表單回應1的B欄 = 活動梯次表的A2欄,也就是表單回應1的報名日期 = 活動梯次表[活動梯次]
-6. [剩餘數量] = [容納數量] - [報名數量]
-7. [額滿否]第一列 = if(H2<=0,"已額滿","尚未額滿"),其餘類推H3,H4,...
最後修改的結果大致如下圖:
步驟五
來寫Google Script
-1. 在Google Sheet 上面選單選擇[工具]->[指令碼編輯器]
-2. 填入以下程式代碼:
// call your form and connect to the drop-down item
var form = FormApp.openById("1cKjOCtj-h7u-S1XHDG5UW72JuK69YTEvb1_9aNjZyCQ");
var namesList = form.getItemById("309397191").asListItem();
var d = new Date();
var date2 = new Date(d.getTime() + (3 * 24 * 60 * 60 * 1000)) //抓取後三天的日期
var datestring = Utilities.formatDate(date2, "UTC+8", "yyyyMMdd"); //改成string
var ss = SpreadsheetApp.getActive();
var names = ss.getSheetByName("活動梯次表");
//從第二列開始抓到getMaxRows() - 1列,抓取第一個欄位值,也就是[活動梯次]這個欄位
var namesValues = names.getRange(2, 1, names.getMaxRows() - 1).getValues();
//從第二列開始抓到getMaxRows() - 1列,抓取第二個欄位值,也就是[日期二]這個欄位
var dateValues = names.getRange(2, 2, names.getMaxRows() - 1).getValues();
//從第二列開始抓到getMaxRows() - 1列,抓取第七個欄位值,也就是[額滿否]這個欄位
var fullflag = names.getRange(2, 7, names.getMaxRows() - 1).getValues(); //是否額滿的註記
var DateOptions = []; //Google Form 關於日期的下拉選單值
j = 0;
// convert the array ignoring empty cells
for(var i = 0; i < namesValues.length; i++)
{
if(dateValues[i][0] >= datestring)
{
if(fullflag[i][0] == '尚未額滿')
{
DateOptions[j] = namesValues[i][0];
j = j + 1;
}
}
if(j >= 10) //只抓後面3-10天
{
break;
}
}
// 產生你的報名日期下拉清單
namesList.setChoiceValues(DateOptions);
[Hint]
這邊有兩行,可能大家不知道去哪抓取
var form = FormApp.openById("這是你的GoogleForm的代碼編號");
var namesList = form.getItemById("這是你的報名日期控制項的ID").asListItem();
GoogleForm的代碼編號就是當你開啟這個GoogleForm編輯狀態時,去看他顯示在瀏覽器的超連結:
https://docs.google.com/forms/d/1cKjOCtj-h7u-S1XHDG5UW72JuK69YTEvb1_9aNjZyCQ/edit#responses
也就是1cKjOCtj-h7u-S1XHDG5UW72JuK69YTEvb1_9aNjZyCQ這一段
至於報名日期控制項的ID要進入瀏覽器的開發人員選項來做觀察,如下圖:
步驟六
再來我們就是要讓Google Script做測試
我把Google Script改成MyFirstApp,gs檔名改成UpDateMyDateItem [這裡都建議不要用中文名稱]
可以先按下執行看看有沒有Bug
若成功的話,應該會如下圖:
報名日期直接顯示未來3-10天的日期
步驟六
到最後一步了,加油...
最後要把專案發佈出去,讓Google自動執行程式
請執行選單[現有專案的執行程序]
然後我建議新增兩種觸發條件,一種是按小時驅動,一種是提交表單後驅動
終於大功告成啦
如果某日的報名人數超過15人,是否下拉選單就不顯示了,大家可以試試看